package com.pzhu.record.constants;

import com.pzhu.record.entity.Achievement;
import com.pzhu.record.entity.Basicinformation;
import com.pzhu.record.entity.Graduation;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.web.multipart.MultipartFile;

import java.io.IOException;
import java.io.InputStream;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.text.DecimalFormat;
import java.util.ArrayList;
import java.util.List;
import java.util.logging.Logger;

/**
 * 读取Excel内容
 *
 * @author Zhou YuSen
 * @date 2020/3/30 10:42
 */
public class ExcelReader<T> {

    private static final String XLS = "xls";
    private static final String XLSX = "xlsx";
    /**
     * 日志打印类
     */
    private static Logger logger = Logger.getLogger(ExcelReader.class.getName());

    /**
     * 根据文件后缀名类型获取对应的工作簿对象
     *
     * @param inputStream 读取文件的输入流
     * @param fileType    文件后缀名类型（xls或xlsx）
     * @return 包含文件数据的工作簿对象
     */
    private static Workbook getWorkbook(InputStream inputStream, String fileType) throws IOException {
        Workbook workbook = null;

        if (XLS.equalsIgnoreCase(fileType)) {
            workbook = new HSSFWorkbook(inputStream);
        } else if (XLSX.equalsIgnoreCase(fileType)) {
            workbook = new XSSFWorkbook(inputStream);
        }
        return workbook;
    }

    /**
     * 将单元格内容转换为字符串
     *
     * @param cell
     * @return
     */
    private static String convertCellValueToString(Cell cell) {
        if (cell == null) {
            return null;
        }
        String returnValue = null;
        switch (cell.getCellType()) {
            //数字
            case NUMERIC:
                double doubleValue = cell.getNumericCellValue();
                // 格式化科学计数法，取一位整数
                returnValue = new DecimalFormat("0").format(doubleValue);
                break;
            //字符串
            case STRING:
                returnValue = cell.getStringCellValue();
                break;
            //布尔
            case BOOLEAN:
                boolean booleanCellValue = cell.getBooleanCellValue();
                returnValue = String.valueOf(booleanCellValue);
                break;
            //空值
            case BLANK:
                break;
            //公式
            case FORMULA:
                returnValue = cell.getCellFormula();
                break;
            //错误
            case ERROR:
                break;
            default:
                break;
        }
        return returnValue;
    }

    /**
     * 获取档案数据,档案寄发
     * 提取每一行中需要的数据，构造成为一个结果数据对象
     * 当该行中有单元格的数据为空或不合法时，忽略该行的数据
     *
     * @param row 行数据
     * @return 解析后的行数据对象，行数据错误时返回null
     */
    private static Graduation convertRowToGraduation(Row row) {
        Graduation graduation = new Graduation();
        Cell cell;
        int cellNum = 0;
        // 获取学号
        cell = row.getCell(cellNum++);
        String Id = convertCellValueToString(cell);
        graduation.setBasicinformationId(Id);
        // 获取回执单号
        cellNum++;
        cell = row.getCell(cellNum++);
        String graduationReturn = convertCellValueToString(cell);
        graduation.setGraduationReturn(graduationReturn);
        // 获取寄发状态
        cell = row.getCell(cellNum++);
        String condition = convertCellValueToString(cell);
        graduation.setGraduationCondition(condition);
        // 获取寄发单位
        cell = row.getCell(cellNum++);
        String unit = convertCellValueToString(cell);
        graduation.setGraduationSendingunit(unit);
        // 获取档案
        cell = row.getCell(cellNum++);
        String graduationLack = convertCellValueToString(cell);
        graduation.setGraduationLack(graduationLack);
        return graduation;
    }

    /**
     * 获取档案数据,档案缺失
     * 提取每一行中需要的数据，构造成为一个结果数据对象
     * 当该行中有单元格的数据为空或不合法时，忽略该行的数据
     *
     * @param row 行数据
     * @return 解析后的行数据对象，行数据错误时返回null
     */
    private static Graduation convertRowToGraduationLack(Row row) {
        Graduation graduation = new Graduation();
        Cell cell;
        int cellNum = 0;
        // 获取学号
        cell = row.getCell(cellNum++);
        String Id = convertCellValueToString(cell);
        graduation.setBasicinformationId(Id);
        // 获取缺失档案
        cell = row.getCell(cellNum++);
        String graduationLack = convertCellValueToString(cell);
        graduation.setGraduationLack(graduationLack);
        return graduation;
    }

    /**
     * 获取x学生基本数据
     * 提取每一行中需要的数据，构造成为一个结果数据对象
     * 当该行中有单元格的数据为空或不合法时，忽略该行的数据
     *
     * @param row 行数据
     * @return 解析后的行数据对象，行数据错误时返回null
     */
    private static Basicinformation convertRowToBasicStu(Row row) {
        Basicinformation basicinformation = new Basicinformation();
        Cell cell;
        int cellNum = 0;
        // 获取学号
        /*cell = row.getCell(cellNum++);
        String basicInformationId  = convertCellValueToString(cell);
        basicinformation.setBasicinformationId(basicInformationId);*/
        // 获取姓名
        cell = row.getCell(cellNum++);
        String basicinformationName = convertCellValueToString(cell);
        basicinformation.setBasicinformationName(basicinformationName);
        // 获取学院
        cell = row.getCell(cellNum++);
        String InstituteName = convertCellValueToString(cell);
        if ("数学与计算机学院".equals(InstituteName)) {
            basicinformation.setInstituteNume("1");
        } else if ("智能制造学院".equals(InstituteName)) {
            basicinformation.setInstituteNume("2");
        } else if ("英语学院".equals(InstituteName)) {
            basicinformation.setInstituteNume("3");
        } else if ("医学院".equals(InstituteName)) {
            basicinformation.setInstituteNume("4");
        }
        // 获取专业班级
        cell = row.getCell(cellNum++);
        String basicinformationClass = convertCellValueToString(cell);
        basicinformation.setBasicinformationClass(basicinformationClass);
        // 获取身份证号
        cell = row.getCell(cellNum++);
        String basicinformationIcard = convertCellValueToString(cell);
        basicinformation.setBasicinformationIcard(basicinformationIcard);
        // 获取家庭住址
        cell = row.getCell(cellNum++);
        String basicinformationHome = convertCellValueToString(cell);
        basicinformation.setBasicinformationHome(basicinformationHome);
        // 获取性别
        cell = row.getCell(cellNum++);
        String basicinformationSex = convertCellValueToString(cell);
        basicinformation.setBasicinformationSex(basicinformationSex);
        // 获取民族
        cell = row.getCell(cellNum++);
        String basicinformationNation = convertCellValueToString(cell);
        basicinformation.setBasicinformationNation(basicinformationNation);
        // 获取联系电话
        cell = row.getCell(cellNum++);
        String basicinformationPhone = convertCellValueToString(cell);
        basicinformation.setBasicinformationPhone(basicinformationPhone);
        // 获取出生地
        cell = row.getCell(cellNum++);
        String basicinformationBirthplace = convertCellValueToString(cell);
        basicinformation.setBasicinformationBirthplace(basicinformationBirthplace);
        // 获取籍贯
        cell = row.getCell(cellNum++);
        String basicinformationNativeplace = convertCellValueToString(cell);
        basicinformation.setBasicinformationNativeplace(basicinformationNativeplace);
        // 获取生源地
        cell = row.getCell(cellNum++);
        String basicinformationSorigin = convertCellValueToString(cell);
        basicinformation.setBasicinformationSorigin(basicinformationSorigin);
        // 获取考生号
        cell = row.getCell(cellNum++);
        String basicinformationCandidatenumber = convertCellValueToString(cell);
        basicinformation.setBasicinformationCandidatenumber(basicinformationCandidatenumber);
        // 获取角色
        cell = row.getCell(cellNum++);
        String basicinformationRole = convertCellValueToString(cell);
        basicinformation.setBasicinformationRole(basicinformationRole);
        // 获取密码
        cell = row.getCell(cellNum++);
        String basicinformationPassword = convertCellValueToString(cell);
        basicinformation.setBasicinformationPassword(basicinformationPassword);
        return basicinformation;
    }

    /**
     * 读取Excel文件内容
     *
     * @param multipartFile 多媒体文件形式
     * @param object        要导入文件的对象
     * @return 读取结果列表，读取失败时返回null
     */
    public List<T> readExcel(MultipartFile multipartFile, Object object) {
        Workbook workbook = null;
        String fileName = null;
        try {
            fileName = multipartFile.getOriginalFilename();
            // 获取Excel后缀名
            String fileType = fileName.substring(fileName.lastIndexOf(".") + 1, fileName.length());
            // 获取Excel工作簿
            workbook = getWorkbook(multipartFile.getInputStream(), fileType);
            // 读取excel中的数据
            return parseExcel(workbook, object);
        } catch (Exception e) {
            logger.warning("解析Excel失败，文件名：" + fileName + " 错误信息：" + e.getMessage());
            e.printStackTrace();
            return null;
        } finally {
            try {
                if (null != workbook) {
                    workbook.close();
                }
            } catch (Exception e) {
                logger.warning("关闭数据流出错！错误信息：" + e.getMessage());
                e.printStackTrace();
            }
        }
    }

    /**
     * 解析Excel数据
     *
     * @param workbook 工作簿对象
     * @return 解析结果
     */
    private List<T> parseExcel(Workbook workbook, Object object) throws NoSuchMethodException, InvocationTargetException, IllegalAccessException {
        List<T> resultDataList = new ArrayList<>();
        Class<?> clazz = resultDataList.getClass();
        Method add = clazz.getMethod("add", Object.class);

        //解析sheet
        for (int sheetNum = 0; sheetNum < workbook.getNumberOfSheets(); sheetNum++) {
            //读取工作表
            Sheet sheet = workbook.getSheetAt(sheetNum);
            // 校验sheet是否合法
            if (null == sheet) {
                continue;
            }
            //获取第一行数据
            int firstRowNum = sheet.getFirstRowNum();
            Row firstRow = sheet.getRow(firstRowNum);
            if (firstRow == null) {
                logger.warning("解析Excel失败，在第一行没有读取到任何数据！");
            }
            //解析每一行数据，构造数据对象
            int rowStart = firstRowNum + 1;
            int end = sheet.getLastRowNum();
            for (int rowNum = rowStart; rowNum <= end; rowNum++) {
                Row row = sheet.getRow(rowNum);
                if (row == null) {
                    continue;
                }
                //判断对象类型
                if (object instanceof Graduation) {
                    Graduation rowToGraduation = null;
                    if (firstRow != null) {
                        int physicalNumberOfCells = firstRow.getPhysicalNumberOfCells();

                        if (physicalNumberOfCells > 3) {
                            rowToGraduation = convertRowToGraduation(row);
                        } else {
                            //档案类型
                            rowToGraduation = convertRowToGraduationLack(row);
                        }
                    }
                    add.invoke(resultDataList, rowToGraduation);
                } else if (object instanceof Basicinformation) {
                    //学生基本信息
                    Basicinformation rowToStu = convertRowToBasicStu(row);
                    add.invoke(resultDataList, rowToStu);
                } else if (object instanceof Achievement) {
                    //成绩表
                    Achievement rowToAchievement = convertRowToBasicrowToAchievement(row);
                    add.invoke(resultDataList, rowToAchievement);
                }
            }
        }
        return resultDataList;
    }

    /**
     * 获取成绩表
     *
     * @param row
     * @return
     */
    private Achievement convertRowToBasicrowToAchievement(Row row) {
        Achievement achievement = new Achievement();
        Cell cell;
        int cellNum = 0;
        // 获取学号
        cell = row.getCell(cellNum++);
        String Id = convertCellValueToString(cell);
        achievement.setBasicinformationId(Id);
        // 获取课程代号
        cellNum++;
        cell = row.getCell(cellNum++);
        String courseCoursenume = convertCellValueToString(cell);
        achievement.setCourseCoursenume(Integer.parseInt(courseCoursenume));
        // 获取成绩
        cell = row.getCell(cellNum++);
        String achievementGrade = convertCellValueToString(cell);
        achievement.setAchievementGrade(Double.valueOf(achievementGrade));
        return achievement;
    }

}
